
import sqlite3
import pandas as pd

# 连接到SQLite数据库
conn = sqlite3.connect('DB_DZZH.db')
# 显示数据库中的所有表
print("Tables:")
print(conn.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall())

## 显示表格结构
#print("\nTable structure:")
#print(conn.execute("PRAGMA table_info(汇总表);").fetchall())
#print("\nTable structure:")
#print(conn.execute("PRAGMA table_info(斜坡孕灾条件主表);").fetchall())
print("\nTable structure:")
print(conn.execute("PRAGMA table_info(切坡建房主表);").fetchall())
#
## 创建一个Cursor对象，用于执行SQL语句
#cursor = conn.cursor()
#
## 查询数据
#cursor.execute("SELECT * FROM 汇总表")
#rows = cursor.fetchall()
#
## 打印查询结果
#for row in rows:
#    print("汇总表echo:", row)
#
## 查询数据
#cursor.execute("SELECT * FROM 斜坡孕灾条件主表")
#rows = cursor.fetchall()
#
## 打印查询结果
#for row in rows:
#    print("斜坡孕灾条件主表echo:", row)
#
#
#
## 更新数据
#cursor.execute("UPDATE 斜坡孕灾条件主表 SET 地点=? WHERE 组=?", ('c', 'b'))
#cursor.execute("SELECT * FROM 斜坡孕灾条件主表")
#rows = cursor.fetchall()
#
## 打印查询结果
#for row in rows:
#    print("change age:", row)
#
#
## 查询数据
#cursor.execute("SELECT * FROM 汇总表")
#rows = cursor.fetchall()
#
## 打印查询结果
#for row in rows:
#    print("汇总表echo:", row)

# # 导入excel数据、生成uuid主键，并写入数据库
# import pandas as pd
# # 读取Excel文件
# df = pd.read_excel('斜坡孕灾条件主表.xlsx')
# # 将DataFrame数据插入数据库
# df.to_sql('斜坡孕灾条件主表', conn, if_exists='append', index=False)

# 创建一个Cursor对象，用于执行SQL语句
cursor = conn.cursor()

# 查询数据
cursor.execute("SELECT * FROM 切坡建房主表")
rows = cursor.fetchall()

print('切坡建房主表')
# 打印查询结果
for row in rows:
    print("切坡建房主表 echo:", row)

# convert database to csv file
query = 'select * from 斜坡孕灾条件主表'
df = pd.read_sql_query(query, conn)

df.to_csv('export.csv', index = False)


# 提交更改
conn.commit()

conn.close()
